<?php

namespace app\services\excel;

use app\dao\office\OfficeStudentDao;
use app\exceptions\AdminException;
use app\services\BaseService;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\facade\Db;
use think\facade\Filesystem;

class ExcelOutEnterService extends BaseService
{

    public function excelEnter($file)
    {
        $filename = Filesystem::disk('public')->putFile('excel', $file);
        if ($filename) { // 上传错误提示错误信息
            // 创建读操作
            // 打开文件、载入excel表格
            $spreadsheet = IOFactory::load($_SERVER['DOCUMENT_ROOT'] . '/file/' . $filename);
            // 获取活动工作薄
            $sheet = $spreadsheet->getActiveSheet();
            // 获取总行数
            $highestRow = $sheet->getHighestRow();
            // 存放插入成功的记录
            $successLog = [];
            // 存放插入失败的记录
            $failLog = [];
            // 从第二行开始读取表格数据，循环写入数据库
            for ($i = 2; $i <= $highestRow; $i++) {//为什么从2开始？：第一排是字段名称比如（id，name,age）
                // 第 1 次循环获取第 1、2、3、4 列 第 2 行单元格的值
                // 第 2 次循环获取第 1、2、3、4  列 第 3 行单元格的值
                // 依次执行
                $name = $sheet->getCellByColumnAndRow(1, $i)->getValue();//重置
                $age = $sheet->getCellByColumnAndRow(2, $i)->getValue();
                $sex = $sheet->getCellByColumnAndRow(3, $i)->getValue();
                // 假设数据表有以下字段
                $data[] = [
                    'id' => $name,
                    'site' => $age,
                    'name' => $sex,
                    'create_time' => date('Y-m-d H:i:s', time())
                ];
            }
//            unlink($_SERVER['DOCUMENT_ROOT'] . '/file/' . $filename);//删除文件
            return "导入数据成功！";
        } else {
            throw new AdminException('文件上传失败！');
        }
    }

    /**
     * 导入学生信息
     * @param string $filename
     * @return array|void
     */
    public function excelEnterStudent(string $filename,$website_id)
    {
        // 创建读操作
        // 打开文件、载入excel表格
        $spreadsheet = IOFactory::load($_SERVER['DOCUMENT_ROOT'] . '/file/' . $filename);
        // 获取活动工作薄
        $sheet = $spreadsheet->getActiveSheet();
        // 获取总行数
        $highestRow = $sheet->getHighestRow();
        // 存放插入失败的记录
        $failLog = [];
        // 从第二行开始读取表格数据，循环写入数据库
        for ($i = 2; $i <= $highestRow; $i++) {
            $major_id = $sheet->getCellByColumnAndRow(1, $i)->getValue();
            $name = $sheet->getCellByColumnAndRow(2, $i)->getValue();
            $heard = $sheet->getCellByColumnAndRow(3, $i)->getValue();
            $sex = $sheet->getCellByColumnAndRow(4, $i)->getValue();
            $adress = $sheet->getCellByColumnAndRow(5, $i)->getValue();
            $postal_code = $sheet->getCellByColumnAndRow(6, $i)->getValue();
            $idcard = $sheet->getCellByColumnAndRow(7, $i)->getValue();
            $phone = $sheet->getCellByColumnAndRow(8, $i)->getValue();
            $exam_number = $sheet->getCellByColumnAndRow(9, $i)->getValue();
            $enter_batch = $sheet->getCellByColumnAndRow(10, $i)->getValue();
            $enter_type = $sheet->getCellByColumnAndRow(11, $i)->getValue();
            $student_number = $sheet->getCellByColumnAndRow(12, $i)->getValue();
            $birthday = $sheet->getCellByColumnAndRow(13, $i)->getValue();
            $political = $sheet->getCellByColumnAndRow(14, $i)->getValue();
            $graduated_school = $sheet->getCellByColumnAndRow(15, $i)->getValue();
            // 假设数据表有以下字段
            $data[] = [
                'major_id' => $major_id,
                'name' => $name,
                'heard' => $heard,
                'sex' => $sex,
                'adress' => $adress,
                'postal_code' => $postal_code,
                'idcard' => $idcard,
                'exam_number' => $exam_number,
                'enter_batch' => $enter_batch,
                'enter_type' => $enter_type,
                'student_number' => $student_number,
                'birthday' => $birthday,
                'political' => $political,
                'graduated_school' => $graduated_school,
                'phone' => $phone,
                'website_id' => $website_id,
                'pwd' => $idcard ? substr($idcard, -6) : ''
            ];
        }
        unlink($_SERVER['DOCUMENT_ROOT'] . '/file/' . $filename);//删除文件
        if (Db::name('free_office_user')->insertAll($data)) {
            return '添加数据成功!';
        }
    }

    public function excelEnterQuestion(string $filename, $website_id)
    {
        // 创建读操作
        // 打开文件、载入excel表格
        $spreadsheet = IOFactory::load($_SERVER['DOCUMENT_ROOT'] . '/file/' . $filename);
        // 获取活动工作薄
        $sheet = $spreadsheet->getSheet(0);
        // 获取总行数
        $highestRow = $sheet->getHighestRow();
        // 从第二行开始读取表格数据，循环写入数据库
        for ($i = 2; $i <= $highestRow; $i++) {
            $title = $sheet->getCellByColumnAndRow(1, $i)->getValue();
            $answer = $sheet->getCellByColumnAndRow(2, $i)->getValue();
            $score = $sheet->getCellByColumnAndRow(3, $i)->getValue();
            $show_type = $sheet->getCellByColumnAndRow(4, $i)->getValue();
            $type = $sheet->getCellByColumnAndRow(5, $i)->getValue();
            $pattern = $sheet->getCellByColumnAndRow(6, $i)->getValue();//阶段测评，综合测评
            $paper_name = $sheet->getCellByColumnAndRow(7, $i)->getValue();//试卷名称
            $A = $sheet->getCellByColumnAndRow(8, $i)->getValue();//试卷名称
            $B = $sheet->getCellByColumnAndRow(9, $i)->getValue();//试卷名称
            $C = $sheet->getCellByColumnAndRow(10, $i)->getValue();//试卷名称
            $D = $sheet->getCellByColumnAndRow(11, $i)->getValue();//试卷名称
            $E = $sheet->getCellByColumnAndRow(12, $i)->getValue();//试卷名称
            $F = $sheet->getCellByColumnAndRow(13, $i)->getValue();//试卷名称

            $data[] = [
                'website_id' => $website_id,
                'title' => $title,
                'show_type' => $show_type,
                'score' => $score,
                'paper_name' => $paper_name,
                'pattern' => $pattern,
                'type' => $type,
                'lenght' => $type == '单选题' || $type == '多选题' ? $this -> topciSum([$A,$B,$C,$D,$E,$F]) : '',
                'dan' => $type == '单选题' ? $answer : '',
                'duo' => $type == '多选题' ? $this->answerSort($answer) : '',
                'pan' => $type == '判断题' ? $answer : '',
                'tian' => $type == '填空题' ? $answer : '',
                'content' => $type == '简答题' || $type == '扫码题'  ? $answer : '',
                'json' => '',
                'A' => $A,
                'B' => $B,
                'C' => $C,
                'D' => $D,
                'E' => $E,
                'F' => $F,
            ];

        }

        unlink($_SERVER['DOCUMENT_ROOT'] . '/file/' . $filename);//删除文件
        if (Db::name('free_exam_question')->insertAll($data)) {
            return '添加数据成功!';
        }


    }


    /**
     * 判断题目长度
     * @param $data
     * @return int
     */
    public function topciSum($data){
        $sum = 0;
        foreach ($data as $val){
            if ($val){
                $sum++;
            }
        }
        return $sum;
    }

    /**
     * 字符串排序
     * @description 将答案 ADCB 转换成 ABCD
     * @param string $string
     * @param string $sort asc|desc
     * @return string
     */
    function answerSort(string $string = '', string $sort = 'asc')
    {
        if (empty($string)) {
            return '';
        }
        //拆分成数组，然后排序
        $arr = explode(',',$string);
        if ($sort == 'asc') {
            sort($arr);//值升序
        } else {
            rsort($arr);//值降序
        }
        //返回新字符串
        return implode(',', $arr);
    }



}